﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;

namespace syc.Produce
{
    public partial class 生产计划录入 : System.Web.UI.Page
    {
        double[] chanliang = new double[12];
        protected HSSFWorkbook hssfworkbook;
        protected void Page_Load(object sender, EventArgs e)
        {
            this.Message.Text = "·准备就绪";
            if (!this.IsPostBack)
            {
                this.NianDu.Text = DateTime.Today.Year.ToString() ;
                this.YueFen.Text = DateTime.Today.Month.ToString();
                string y = Request.QueryString["y"];
                string m = Request.QueryString["m"];

                if (y == null || m == null || this.NianDu.Items.FindByValue(y) == null || this.YueFen.Items.FindByValue(m) == null)
                {

                }
                else
                {
                    int year = int.Parse(y);
                    int month = int.Parse(m);
                    this.NianDu.Text = y; 
                    this.YueFen.Text = m;

                    SqlConnection Conn = new SqlConnection();
                    Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
                    Conn.Open();
                    SqlCommand Cmd = new SqlCommand();
                    Cmd.Connection = Conn;

                    Cmd.CommandText = "select * from 生产计划 where 年度="+y+" and 月份="+m;
                    Cmd.CommandType = CommandType.Text;
                    SqlDataReader Reader = Cmd.ExecuteReader();
                    if (Reader.Read())
                    {
                        this.ChanLiang12.Text = ((double)Reader["一分厂产品2"]).ToString() ;
                        this.ChanLiang21.Text = ((double)Reader["二分厂产品1"]).ToString();
                        this.ChanLiang22.Text = ((double)Reader["二分厂产品2"]).ToString();
                        this.ChanLiang31.Text = ((double)Reader["三分厂产品1"]).ToString();
                        this.ChanLiang32.Text = ((double)Reader["三分厂产品2"]).ToString();
                        this.ChanLiang42.Text = ((double)Reader["四分厂产品2"]).ToString();
                        this.ChanLiang51.Text = ((double)Reader["五分厂产品1"]).ToString();
                        this.WaiGou1.Text = ((double)Reader["外购接箍1"]).ToString();
                        this.WaiGou2.Text = ((double)Reader["外购接箍2"]).ToString();

                        this.NianDu.Enabled = false;
                        this.YueFen.Enabled = false;
                    }
                    Reader.Close();

                    Conn.Close();
                    Reader.Dispose();
                    Cmd.Dispose();
                    Conn.Dispose();


                }
            }
        }
        private bool CheckInput()
        {
            if (!double.TryParse(this.ChanLiang12.Text, out chanliang[1]))
            {
                this.Message.Text = "·一分厂 套管管体 计划产量录入不正确";
                return false;
            }
            if (!double.TryParse(this.ChanLiang21.Text, out chanliang[2]))
            {
                this.Message.Text = "·二分厂 油管管体 计划产量录入不正确";
                return false;
            }
            if (!double.TryParse(this.ChanLiang22.Text, out chanliang[3]))
            {
                this.Message.Text = "·二分厂 套管管体 计划产量录入不正确";
                return false;
            }
            if (!double.TryParse(this.ChanLiang31.Text, out chanliang[4]))
            {
                this.Message.Text = "·三分厂 油管接箍 计划产量录入不正确";
                return false;
            }
            if (!double.TryParse(this.ChanLiang32.Text, out chanliang[5]))
            {
                this.Message.Text = "·三分厂 套管接箍 计划产量录入不正确";
                return false;
            }
            if (!double.TryParse(this.ChanLiang42.Text, out chanliang[7]))
            {
                this.Message.Text = "·四分厂 套管保护器 计划产量录入不正确";
                return false;
            }
            if (!double.TryParse(this.ChanLiang51.Text, out chanliang[8]))
            {
                this.Message.Text = "·五分厂 油管管体 计划产量录入不正确";
                return false;
            }
            if (!double.TryParse(this.WaiGou1.Text, out chanliang[10]))
            {
                this.Message.Text = "·外购接箍 油管接箍 计划量录入不正确";
                return false;
            }
            if (!double.TryParse(this.WaiGou2.Text, out chanliang[11]))
            {
                this.Message.Text = "·外购接箍 套管接箍 计划量录入不正确";
                return false;
            }
            return true;
        }
        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("模板/生产计划.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "陕西延长石油材料有限公司";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "生产计划";
            hssfworkbook.SummaryInformation = si;
        }
        protected void WriteToFile()
        {
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + Server.UrlEncode("生产计划")+".xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }
        protected void Button_Save_Click(object sender, EventArgs e)
        {

            if (!CheckInput())
            {
                return;
            }

            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;

            if (this.NianDu.Enabled)
            {

                Cmd.CommandText =
"if EXISTS(select * from 生产计划 where 年度=" + this.NianDu.Text + " and 月份=" + this.YueFen.Text + ")\n"
+ "begin\n"
+ "return\n"
+ "end\n"
+ "else\n"
+ "begin\n"
+ "insert into 生产计划(年度,月份,一分厂产品1,一分厂产品2,二分厂产品1,二分厂产品2,"
+ "三分厂产品1,三分厂产品2,四分厂产品1,四分厂产品2,五分厂产品1,五分厂产品2,外购接箍1,外购接箍2)"
+ "values(" + this.NianDu.Text + "," + this.YueFen.Text
+ "," + chanliang[0]
+ "," + chanliang[1]
+ "," + chanliang[2]
+ "," + chanliang[3]
+ "," + chanliang[4]
+ "," + chanliang[5]
+ "," + chanliang[6]
+ "," + chanliang[7]
+ "," + chanliang[8]
+ "," + chanliang[9]
+ "," + chanliang[10]
+ "," + chanliang[11]
+ ")\n"
+ "end";
            }
            else
            {
                Cmd.CommandText =
"update 生产计划 set 一分厂产品1=" + chanliang[0]
+ ",一分厂产品2=" + chanliang[1]
+ ",二分厂产品1=" + chanliang[2]
+ ",二分厂产品2=" + chanliang[3]
+ ",三分厂产品1=" + chanliang[4]
+ ",三分厂产品2=" + chanliang[5]
+ ",四分厂产品1=" + chanliang[6]
+ ",四分厂产品2=" + chanliang[7]
+ ",五分厂产品1=" + chanliang[8]
+ ",五分厂产品2=" + chanliang[9]
+ ",外购接箍1=" + chanliang[10]
+ ",外购接箍2=" + chanliang[11] + " where 年度="
+ this.NianDu.Text + " and 月份=" + this.YueFen.Text;
            }


            if (Cmd.ExecuteNonQuery() < 1)
            {
                if (this.NianDu.Enabled)
                {
                    this.Message.Text = "·保存失败，计划已经存在";
                }
                else
                {
                    this.Message.Text = "·保存失败，您正在修改的计划可能已经被删除";
                }
            }
            else
            {
                this.Message.Text = "·保存成功";
                this.NianDu.Enabled = false;
                this.YueFen.Enabled = false;
            }

            Conn.Close();
            Cmd.Dispose();
            Conn.Dispose();

        }

        protected void Button_Export_Click(object sender, EventArgs e)
        {
            if (!CheckInput())
            {
                return;
            }
            InitializeWorkbook();

            HSSFSheet sheet1 = hssfworkbook.GetSheet("数据");
            int r = 0;
            HSSFRow row;
            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("计划");
            row.CreateCell(1).SetCellValue(this.NianDu.SelectedItem.Text+"年"+this.YueFen.SelectedItem.Text);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("一分厂油管");
            row.CreateCell(1).SetCellValue(chanliang[0]);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("一分厂套管");
            row.CreateCell(1).SetCellValue(chanliang[1]);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("二分厂油管");
            row.CreateCell(1).SetCellValue(chanliang[2]);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("二分厂套管");
            row.CreateCell(1).SetCellValue(chanliang[3]);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("三分厂油管接箍");
            row.CreateCell(1).SetCellValue(chanliang[4]);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("三分厂套管接箍");
            row.CreateCell(1).SetCellValue(chanliang[5]);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("四分厂油管螺纹保护器");
            row.CreateCell(1).SetCellValue(chanliang[6]);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("四分厂套管螺纹保护器");
            row.CreateCell(1).SetCellValue(chanliang[7]);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("五分厂油管");
            row.CreateCell(1).SetCellValue(chanliang[8]);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("五分厂套管");
            row.CreateCell(1).SetCellValue(chanliang[9]);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("外购油管接箍");
            row.CreateCell(1).SetCellValue(chanliang[10]);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("外购套管接箍");
            row.CreateCell(1).SetCellValue(chanliang[11]);


            HSSFSheet sheet2 = hssfworkbook.GetSheet("报表");
            sheet2.ForceFormulaRecalculation = true;
            WriteToFile();
            Response.End();
        }
    }
}
